package gob.osinerg.emergencia.dao;

import gob.osinerg.emergencia.bean.AccidenteGraveFinalTestigo;
import gob.osinerg.emergencia.bean.FormularioEmergenciaUnmay;
import gob.osinerg.emergencia.bean.SiniestroFinal;
import gob.osinerg.emergencia.bean.SiniestroFinalAcCorrectiva;
import gob.osinerg.emergencia.bean.SiniestroFinalAcPreventiva;
import gob.osinerg.emergencia.bean.SiniestroFinalCsaSnstro;
import gob.osinerg.emergencia.bean.SiniestroPreliminar;
import gob.osinerg.emergencia.bean.SiniestroPreliminarAccidentado;
import gob.osinerg.emergencia.bean.TipoSiniestro;

import java.io.File;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

public class SiniestroFinalDAOImpl extends GenericDAOImpl implements SiniestroFinalDAO{

	public void save(SiniestroFinal bean, FormularioEmergenciaUnmay beanform) throws Exception {
		Session session = getSession();
		Transaction transaction = null;
		DecimalFormat formateador = new DecimalFormat("0000");
		String idArchivo = null;
		
	    InputStream inputStream = null;
	    Properties properties = null;
	    Map map = null;
		String rutaFile = null;
		String pathFile = null;
		SiniestroFinalCsaSnstro csaSnstro = null;
		SiniestroFinalAcPreventiva acPreventiva = null;
		SiniestroFinalAcCorrectiva acCorrectiva = null;
		
		try {
			transaction = session.beginTransaction();
			
		    inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("gob/osinerg/resources/parameter.properties");
		    properties = new Properties();
		    properties.load(inputStream);
		    map = properties;
		    pathFile = (String)map.get("directorio.archivos.emergencia.siniestroFinal");
		    
			if(beanform.getId() == null){
				beanform.setId(getNextPK("FRM_EMR_UM_SEQ"));
			}
			
			if(bean.getId() == null){
				bean.setId(getNextPK("SINIE_FIN_SEQ"));
			}
			
			if(bean.getIdFormularioEmergencia()==null) {
				if(beanform.getNmroEmrgncia() == null){
					beanform.setNmroEmrgncia(bean.getPreliminar().getIdFormato());
				}
				session.saveOrUpdate(beanform);
				bean.setIdFormularioEmergencia(beanform.getId());
			}
			
		    idArchivo = "F5"+formateador.format(bean.getId());			
			
			session.saveOrUpdate(bean);
			
			for(int i=0;i < bean.getListaCausaSiniestro().size();i++){
				csaSnstro = (SiniestroFinalCsaSnstro)bean.getListaCausaSiniestro().get(i);
				if(csaSnstro.getId()==null){
					csaSnstro.setId(getNextPK("SNSTRO_FNAL_CSAS_SEQ"));
					csaSnstro.setIdFormularioEmergencia(bean.getId());
				}
				session.saveOrUpdate(csaSnstro);
			}			
			
			for(int i=0;i < bean.getAccionesPreventivas().size();i++){
				acPreventiva = (SiniestroFinalAcPreventiva)bean.getAccionesPreventivas().get(i);
				if(acPreventiva.getId()==null){
					acPreventiva.setId(getNextPK("SNSTRO_FNAL_AP_SEQ"));
					acPreventiva.setIdFormularioEmergencia(bean.getId());
					
					rutaFile = pathFile + File.separator + idArchivo+"_"+acPreventiva.getArchivo().getFileName();
					acPreventiva.setRuta(rutaFile);
				}
				session.saveOrUpdate(acPreventiva);
			}
			
			for(int i=0;i < bean.getAccionesCorrectivas().size();i++){
				acCorrectiva = (SiniestroFinalAcCorrectiva)bean.getAccionesCorrectivas().get(i);
				if(acCorrectiva.getId()==null){
					acCorrectiva.setId(getNextPK("SNSTRO_FNAL_AC_SEQ"));
					acCorrectiva.setIdFormularioEmergencia(bean.getId());
					
					rutaFile = pathFile + File.separator + idArchivo+"_"+acCorrectiva.getArchivo().getFileName();
					acCorrectiva.setRuta(rutaFile);
				}
				session.saveOrUpdate(acCorrectiva);
			}
			
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
			throw e;
		}finally{
			session.close();
		}
	}
	
	public Long getNextPK(String sequence) throws Exception {
        long result = 1;

        StringBuffer sql = new StringBuffer("");
        sql.append("SELECT ");
        sql.append(sequence);
        sql.append(".NEXTVAL AS ID FROM DUAL");        
        
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = getSession().connection().prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            if(rs.next()) {
                result = rs.getLong(1);
            }
            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;
        }
        catch(Exception e) {
            log.error(e.getMessage());
            if(pstmt != null) {
                try {pstmt.close();} catch(Exception ignore) {}
            }
            if(rs != null) {
            	try {rs.close();}catch(Exception ignore) {}
            }
        }
        return new Long(result);
    }
	
	
	public List listarTipoSiniestro() throws Exception {
        List lista = new ArrayList();
        TipoSiniestro tipoSiniestro = null;

        StringBuffer sql = new StringBuffer("");
        sql.append(" SELECT " +
        		   " NSFH_IDSEQ, RV_MEANING " +
        		   " FROM CG_REF_CODES " +
        		   " WHERE RV_DOMAIN = 'FRMTO_DOS_TPO_SNSTRO' " +
        		   " ORDER BY RV_DOMAIN ASC ");        
        
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = getSession().connection().prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            while(rs.next()) {
            	tipoSiniestro = new TipoSiniestro();
            	tipoSiniestro.setIdSiniestro(new Long(rs.getLong(1)));
            	tipoSiniestro.setDescripcion(rs.getString(2));
            	lista.add(tipoSiniestro);
            }
            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;
        }
        catch(Exception e) {
            log.error(e.getMessage());
            if(pstmt != null) {
                try {pstmt.close();} catch(Exception ignore) {}
            }
            if(rs != null) {
            	try {rs.close();}catch(Exception ignore) {}
            }
        }
        
        return lista;
    }
	
	
	public List findFormEmergenciaUnmay(String usrioCrdor, Long frmlarioId) throws Exception {			
			Criteria criteria = getSession().createCriteria(FormularioEmergenciaUnmay.class);
				criteria.add(Restrictions.eq("usrioCrdor",usrioCrdor));
				criteria.add(Restrictions.eq("frmlarioId",frmlarioId));
			criteria.addOrder(Order.asc("nmroEmrgncia"));
			
			return criteria.list();
    }
	
	public List findSiniestroPreliminar(String usrioCrdor, Long frmlarioId) throws Exception {			
			
		 String hql = " select s from " +
		 			  " SiniestroPreliminar s " +
		 			  " where s.id not in (" +
		 			  " select pre.id from SiniestroPreliminar pre,  SiniestroFinal fi " +
		 			  " where pre.id = fi.preliminar.id)" +
		 			  " order by s.id " ;

		 Query query = getSession().createQuery(hql);
		
		return query.list();
	}
	
	public SiniestroPreliminar findSiniestroPreliminar(Long id) throws Exception {			
		Criteria criteria = getSession().createCriteria(SiniestroPreliminar.class);
		criteria.add(Restrictions.eq("id",id));
		
		return (SiniestroPreliminar)criteria.uniqueResult();
	}
	
	public List findSiniestroFinal(String usrioCrdor, Long frmlarioId) throws Exception {			
		
		 String hql = " select s from " +
		 			  " SiniestroFinal s " +
		 			  " where s.flagTemporal = 'SI'" +
		 			  " order by s.id ";

		 Query query = getSession().createQuery(hql);
		
		return query.list();
	}
	
	public SiniestroFinal findSiniestroFinal(Long id) throws Exception {		
		SiniestroFinal siniestroFinal = null;
		
		Criteria criteria = getSession().createCriteria(SiniestroFinal.class);
		criteria.add(Restrictions.eq("id",id));
		siniestroFinal = (SiniestroFinal)criteria.uniqueResult();
		
		Criteria criteriaCausasSiniestro = getSession().createCriteria(SiniestroFinalCsaSnstro.class);
		criteriaCausasSiniestro.add(Restrictions.eq("idFormularioEmergencia",siniestroFinal.getId()));
		List listaCausaSiniestro = criteriaCausasSiniestro.list();
		
		Criteria criteriaAccionesPreventivas = getSession().createCriteria(SiniestroFinalAcPreventiva.class);
		criteriaAccionesPreventivas.add(Restrictions.eq("idFormularioEmergencia",siniestroFinal.getId()));
		List accionesPreventivas = criteriaAccionesPreventivas.list();

		Criteria criteriaAccionesCorrectivas= getSession().createCriteria(SiniestroFinalAcCorrectiva.class);
		criteriaAccionesCorrectivas.add(Restrictions.eq("idFormularioEmergencia",siniestroFinal.getId()));
		List accionesCorrectivas = criteriaAccionesCorrectivas.list();
		
		if(siniestroFinal!=null) {
			siniestroFinal.setListaCausaSiniestro(listaCausaSiniestro);
			siniestroFinal.setAccionesPreventivas(accionesPreventivas);
			siniestroFinal.setAccionesCorrectivas(accionesCorrectivas);
		}

		return siniestroFinal;
	}

	@Override
	public Long obtenerIdSiniestroFinal(Long idFormulario) throws Exception {
		// TODO Auto-generated method stub
		String hql = " select s.id from " +
	 			  " SiniestroFinal s " +
	 			  " where s.idFormularioEmergencia = "+idFormulario;

	 Query query = getSession().createQuery(hql);
	
	return Long.parseLong(query.uniqueResult().toString());
	}

} 